﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;


using System.Data.SQLite;
using System.Data;
using System.Data.Common;

namespace EasyUIDemo1
{
    public static class SQLiteHelper
        
    {
        private static readonly string prefix = "@";
        private static SQLiteConnection connection;
        private static SQLiteConnection Connection
        {
            get
            {
                if (connection == null)
                {
                    string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SQLiteConnectionString"].ConnectionString;
                    connection = new SQLiteConnection(connectionString);
                }
                return connection;
            }
        }

        public static void AddParameter(IDbCommand cmd, string name, DbType type, object value)
        {
            IDbDataParameter parameter = cmd.CreateParameter();

            parameter.DbType = type;
            parameter.ParameterName = prefix + name;
            parameter.Value = value;
            //parameter.Size = 2;

            cmd.Parameters.Add(parameter);
        }

        #region ExecuteNonQuery

        public static int ExecuteNonQuery(string commandText)
        {
            return ExecuteNonQuery(commandText, null);
        }

        public static int ExecuteNonQuery(string commandText, params IDbDataParameter[] parameters)
        {
            int effectRows = 0;
            using (IDbConnection cn = Connection)
            using (IDbCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = commandText;

                foreach (IDbDataParameter p in parameters)
                {
                    cmd.Parameters.Add(p);
                }

                //PopulateParameters(cmd, GetBilingual());
                cn.Open();
                effectRows = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cn.Close();
            }

            return effectRows;
        }

        public static int ExecuteNonQuery<T>(string commandText, T t, Action<IDbCommand, T> populateParameters)
        {
            int effectRows = 0;
            using (IDbConnection cn = Connection)
            using (IDbCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = commandText;

                if (populateParameters != null)
                {
                    populateParameters(cmd, t);
                }


                //PopulateParameters(cmd, GetBilingual());
                cn.Open();
                effectRows = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cn.Close();
            }

            return effectRows;
        }

        public static int ExecuteNonQuery(string commandText1, string commandText2, string c)
        {
            int effectRows = 0;
            using (IDbConnection cn = Connection)
            {
                cn.Open();//
                using (IDbCommand cmd = cn.CreateCommand())
                using (IDbTransaction trans = cn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
                {
                    cmd.Transaction = trans;
                    try
                    {
                        cmd.CommandText = commandText1;
                        effectRows = cmd.ExecuteNonQuery();
                        cmd.CommandText = commandText2;
                        effectRows += cmd.ExecuteNonQuery();
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                    }
                    finally
                    {
                        cn.Close();
                    }
                }
            }

            return effectRows;
        }

        public static int BulkInset<T>(string commandText, IEnumerable<T> list, Action<IDbCommand, T> populateParameters) where T : class
        {
            int effectRows = 0;
            using (IDbConnection cn = Connection)
            using (IDbCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = commandText;
                cn.Open();

                //IDbTransaction trans = cn.BeginTransaction();
                using (IDbTransaction trans = cn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
                {
                    cmd.Transaction = trans;

                    try
                    {
                        foreach (T line in list)
                        {
                            if (line != null)
                            {
                                populateParameters(cmd, line);
                            }

                            effectRows += cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }

                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                    }
                    finally
                    {
                        cn.Close();
                    }
                }

            }
            return effectRows;
        }

        #endregion

        #region ExecuteScalar

        public static object ExecuteScalar(string commandText, params IDbDataParameter[] parameters)
        {
            object obj = null;
            using (IDbConnection cn = Connection)
            using (IDbCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = commandText;

                if (parameters != null && parameters.Length > 0)
                {
                    foreach (IDbDataParameter p in parameters)
                    {
                        cmd.Parameters.Add(p);
                    }
                }

                //PopulateParameters(cmd, GetBilingual());
                cn.Open();
                obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                cn.Close();
            }

            return obj;
        }

        #endregion

        #region ExecuteReader

        public static IDataReader ExecuteReader(string commandText, params IDbDataParameter[] parameters)
        {
            IDataReader obj = null;

            IDbConnection cn = Connection;
            IDbCommand cmd = cn.CreateCommand();

            cmd.CommandText = commandText;

            foreach (IDbDataParameter p in parameters)
            {
                cmd.Parameters.Add(p);
            }

            //PopulateParameters(cmd, GetBilingual());
            cn.Open();
            obj = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            //cn.Close();

            return obj;
        }

        public static List<T> GetList<T>(string commandText, T entity, Action<IDbCommand, T> populateParameters, Action<T, IDataReader> loadEntityInfo) where T : class, new()
        {
            List<T> list = new List<T>();
            using (IDbConnection cn = Connection)
            using (IDbCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = commandText;

                if (populateParameters != null)
                {
                    populateParameters(cmd, entity);
                }

                //PopulateParameters(cmd, GetBilingual());
                cn.Open();
                IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    T t = new T();
                    loadEntityInfo(t, reader);

                    list.Add(t);
                }
                cmd.Parameters.Clear();
                cn.Close();
            }

            return list;
        }

        #endregion

        #region ExecuteDataSet

        public static DataSet ExecuteDataSet(string commandText, params IDbDataParameter[] parameters)
        {
            DataSet ds = new DataSet();
            using (IDbConnection cn = Connection)
            using (IDbCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = commandText;

                DbDataAdapter da = new SQLiteDataAdapter((SQLiteCommand)cmd);
                da.Fill(ds);
                cmd.Parameters.Clear();
            }

            return ds;

        }

        #endregion
    }
}
